/****** Object:  StoredProcedure [dbo].[tool_SelectKeHoachNotInDonViChiTieu]    Script Date: 5/13/2016 11:43:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		HuyNV23
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[tool_SelectKeHoachNotInDonViChiTieu]
	-- Add the parameters for the stored procedure here
	@NAM_KE_HOACH_ID int,
	@STATUS_ID smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    select a1.[DON_VI_ID],a1.[CHI_TIEU_ID], a2.[TEN_CHI_TIEU_CAP1], a2.[TEN_CHI_TIEU_CAP2] , a2.TEN_CHI_TIEU 
	from [NV].[DONVI_CHITIEU] as a1
	inner join [DM].[CHITIEU] as a2 on a1.CHI_TIEU_ID = a2.CHI_TIEU_ID
	where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	and a1.[FLAG] = 0
	and a2.[TYPE] = 1
	and a1.[DON_VI_CHI_TIEU_ID] not in
	(select dvct.[DON_VI_CHI_TIEU_ID]
	from [NV].[DONVI_CHITIEU] as dvct
	inner join [NV].[KEHOACH] as kh on kh.CHI_TIEU_ID = dvct.CHI_TIEU_ID and kh.DON_VI_ID = dvct.DON_VI_ID
	where dvct.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	and dvct.FLAG = 0
	and kh.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID
	and kh.[STATUS_ID] = @STATUS_ID)
END
